﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Timers;
using System.Threading;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;

namespace UNSWThink
{
   /// <summary>
   /// Wrapper object that performs all of the necessary database work using the Npgsql API.
   /// </summary>
   public class DBConnection
   {
      private string server;
      private string port;
      private string userID;
      private string password;
      private string dbName;

      private NpgsqlConnection dbConn;
      private string resultMsg;

      /// <summary>
      /// Default constructor for DBConnection. Uses settings located in file "app.config"
      /// </summary>
      public DBConnection()
      {
         server = ConfigurationSettings.AppSettings["ServerAddress"];
         port = ConfigurationSettings.AppSettings["ServerPort"];
         userID = ConfigurationSettings.AppSettings["UserID"];
         password = ConfigurationSettings.AppSettings["Password"];
         dbName = ConfigurationSettings.AppSettings["DBName"];
      }

      /// <summary>
      /// Alternate constructor for DBConnection.
      /// </summary>
      /// <param name="serverIPAddress">
      /// IP address used to connect to the server where the database is located.
      /// </param>
      /// <param name="serverPort">
      /// Port used to connect to the server where the database is located.
      /// </param>
      /// <param name="serverUsername">
      /// Username used to connect to the server and access the database.
      /// </param>
      /// <param name="serverPassword">
      /// Password used to connect to the server and access the database.
      /// </param>
      /// <param name="databaseName">
      /// Name of the database that the user wants to connect to.
      /// </param>
      public DBConnection(string serverIPAddress, string serverPort, string serverUsername, string serverPassword, string databaseName)
      {
         server = serverIPAddress;
         port = serverPort;
         userID = serverUsername;
         password = serverPassword;
         dbName = databaseName;
      }

      #region CONNECTION METHODS

      /// <summary>
      /// Open a connection to a database specified by the constructors.
      /// </summary>
      /// <returns>
      /// Returns whether or not the connection was successful.
      /// </returns>
      public bool Connect()
      {
         bool result = false;
         try
         {
            //string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};SSL=true;SSLMODE=Require;", server, port, userID, password, dbName);
            string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", server, port, userID, password, dbName);
            dbConn = new NpgsqlConnection(connectionString);
            dbConn.Open();

            resultMsg = "Successfully connected to " + dbName + " @ " + server + " on port " + port;
            result = true;
         }
         catch (Exception error)
         {
            Console.WriteLine(error.ToString());
            //throw;

            resultMsg = "Unsuccessful connection to " + dbName + " @ " + server + " on port " + port;
            result = false;
         }

         return result;
      }

      /// <summary>
      /// Closes a connection to a database.
      /// </summary>
      /// <returns>
      /// Returns whether or not the disconnection was successful.
      /// </returns>
      public bool Disconnect()
      {
         bool result = false;

         if (CheckConnection())
         {
            try
            {
               dbConn.Close();

               resultMsg = "Successfully closed connection to " + dbName + " @ " + server + " on port " + port;
               result = true;
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());
               //throw;

               resultMsg = "Unsuccessful closing of connection to " + dbName + " @ " + server + " on port " + port;
               result = false;
            }
         }

         return result;
      }

      /// <summary>
      /// Checks if connection to a database exists and peforms a connection if it doesn't exist.
      /// </summary>
      /// <returns>
      /// Returns whether or not an open connection exists.
      /// </returns>
      public bool CheckConnection()
      {
         bool result = false;

         if (dbConn == null)
         {
            Connect();
         }

         string connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", server, port, userID, password, dbName);
         NpgsqlConnection testConnection = new NpgsqlConnection(connectionString);
         
         if (testConnection != null)
         {
            try
            {
               testConnection.Open();
               /*if (testConnection.State == ConnectionState.Open)
               {*/
                  result = true;
                  testConnection.Close();
               /*}
               else
               {
                  result = false;
               }*/
            }
            catch (NpgsqlException nError)
            {
               Console.WriteLine(nError.ToString());

               resultMsg = nError.ToString();
               result = false;
            }
         }

         return result;
      }

      #endregion

      #region QUERY METHODS

      /// <summary>
      /// Performs a query to the database to determine if the given username and password exists
      /// </summary>
      /// <param name="insUsername">
      /// User's username
      /// </param>
      /// <param name="insPassword">
      /// User's password
      /// </param>
      /// <param name="insID">
      /// Reference to an external integer - to return an instructor ID
      /// </param>
      /// <returns>
      /// Boolean result corresponding to whether the given details exist or not
      /// </returns>
      public bool AttemptInstuctorLogin(string insUsername, string insPassword, ref int insID)
      {
         bool result = false;

         if (CheckConnection())
         {
            try
            {
               string sql = "SELECT id FROM IndividualAccount WHERE username = :value0 AND password = :value1 AND type = 'INS'";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add("value0", NpgsqlDbType.Text);
               command.Parameters.Add("value1", NpgsqlDbType.Text);
               command.Parameters[0].Value = insUsername;
               command.Parameters[1].Value = insPassword;
               
               object returnedValue = command.ExecuteScalar();
               if (returnedValue != null)
               {
                  insID = Int32.Parse(returnedValue.ToString());
               }
               else
               {
                  insID = 0;
               }

               if (insID > 0)
               {
                  result = true;
                  resultMsg = "Successful login";
               }
               else
               {
                  result = false;
                  resultMsg = "Unsuccessful login";
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return result;
      }

      /// <summary>
      /// Performs a query to return all user generated table names.
      /// </summary>
      /// <returns>
      /// ArrayList of string table names
      /// </returns>
      public ArrayList GetTableNames()
      {
         ArrayList tableNames = new ArrayList();

         if (CheckConnection())
         {
            try
            {
               // sql query
               string sql = "SELECT * FROM DBTableNames";

               // create command with sql query string and db connection
               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               // loop through each returned result and add to ArrayList
               NpgsqlDataReader dr = command.ExecuteReader();
               while (dr.Read())
               {
                  tableNames.Add(dr[0].ToString());
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return tableNames;
      }

      //public ArrayList GetColumnNames(string tableName)
      /// <summary>
      /// Performs a query to return all of the column names of a specified table.
      /// </summary>
      /// <param name="tableName">
      /// Name of the table to perform query.
      /// </param>
      /// <returns>
      /// ArrayList of string column names.
      /// </returns>
      public ArrayList GetColumnNames(string tableName)
      {
         ArrayList columnNames = new ArrayList();

         if (CheckConnection())
         {
            try
            {
               string sql = "SELECT * FROM TableColumnNames(:value0)";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               // Now add the parameter to the parameter collection of the command specifying its type.
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Text));

               // Now, add a value to it and later execute the command as usual.
               command.Parameters[0].Value = tableName;

               NpgsqlDataReader dr = command.ExecuteReader();
               while (dr.Read())
               {
                  columnNames.Add(dr[0].ToString());
                  //string columnName = dr[0].ToString();
                  //columnNames.Add(columnName, columnName);
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return columnNames;
      }

      /// <summary>
      /// Performs a query to return all data from a specified table.
      /// </summary>
      /// <param name="tableName">
      /// Name of table to perform query.
      /// </param>
      /// <returns>
      /// DataTable containing all data from table.
      /// </returns>
      public DataTable GetTableData(string tableName)
      {
         DataTable dt = new DataTable();

         if (CheckConnection())
         {
            try
            {
               string sql = "SELECT * FROM " + tableName;

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Text));

               // Now, add a value to it and later execute the command as usual.
               command.Parameters[0].Value = tableName;

               // store result in DataSet
               DataSet ds = new DataSet();
               da.Fill(ds);

               // set DataTable with first table in DataSet
               if (ds.Tables.Count > 0)
               {
                  dt = ds.Tables[0];
               }

               // serach for "id" column and set it to "read-only"
               for (int i = 0; i < dt.Columns.Count; i++)
               {
                  if (dt.Columns[i].ColumnName.ToLower() == "id")
                  {
                     dt.Columns[i].ReadOnly = true;
                     break;
                  }
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return dt;
      }

      /// <summary>
      /// Makes a query to the connected database to retrieve all data relating to the instructor
      /// </summary>
      /// <param name="instructorID">
      /// ID of the instructor that is querying the database
      /// </param>
      /// <returns>
      /// DataSet of all the data relating to the instructor. Includes class, course, subject, and term information
      /// </returns>
      public DataSet GetAllInstructorRelatedData(int instructorID)
      {
         DataSet newData = new DataSet();

         if (CheckConnection())
         {
            try
            {
               // Get all classes taught by the instructor
               string sql = "SELECT DISTINCT cl.id, cl.course, cl.starttime, cl.finishtime, cl.week, cl.name, cl.comments FROM Class cl " +
                  "JOIN Teaches t ON ((t.account = :value0) AND (t.class = cl.id)) " + 
                  "ORDER BY cl.starttime";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
               DataTable dt = new DataTable("Class");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all courses related to classes being taught by the instructor
               sql = "SELECT DISTINCT c.id, c.subject, c.term FROM Course c " +
                  "JOIN Teaches t ON (t.account = :value0) " +
                  "JOIN Class cl ON ((cl.id = t.class) AND (cl.course = c.id))";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Course");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all subjects related to the courses taught by the instructor
               sql = "SELECT DISTINCT s.id, s.name, s.code FROM Subject s " +
                  "JOIN Teaches t ON (t.account = :value0) " +
                  "JOIN Class cl ON (cl.id = t.class) " +
                  "JOIN Course c ON ((c.subject = s.id) AND (c.id = cl.course))";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Subject");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all terms related to the courses taught by the instructor
               sql = "SELECT DISTINCT tm.id, tm.session, tm.startdate, tm.finishdate FROM Term tm " +
                  "JOIN Teaches t ON (t.account = :value0) " +
                  "JOIN Class cl ON (cl.id = t.class) " +
                  "JOIN Course c ON ((c.term = tm.id) AND (c.id = cl.course)) " +
                  "ORDER BY tm.startdate";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Term");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all questions related to the classes taught by the instructor
               sql = "SELECT DISTINCT q.id, q.class, q.number, q.description, q.type, q.image FROM Question q " +
                  "JOIN Teaches t ON ((t.account = :value0) AND (t.class = q.class))";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Question");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all answers related to the questions belonging to the classes taught by the instructor
               sql = "SELECT DISTINCT a.id, a.question, a.description, a.isCorrect, a.image FROM Answer a " +
                  "JOIN Teaches t ON (t.account = :value0) " +
                  "JOIN Question q ON ((q.class = t.class) AND (q.id = a.question))";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Answer");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get all class accounts related to the classes that the instructor teaches in
               sql = "SELECT ca.id, ca.class, ca.password FROM ClassAccount ca " +
                  "JOIN Teaches t ON ((t.account = :value0) AND (ca.class = t.class))";

               command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = instructorID;

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("ClassAccount");
               da.Fill(dt);
               newData.Tables.Add(dt);
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return newData;
      }

      /// <summary>
      /// Makes a query to the connected database to retrieve responses
      /// </summary>
      /// <param name="offset">
      /// Number of rows to skip so that existing data is not retrieved again
      /// </param>
      /// <param name="classID">
      /// ID of the class that is related to the responses
      /// </param>
      /// <param name="currentDT">
      /// Existing data table that contains a local copy of the response data.
      /// Used to merge data from the query with the existing data
      /// </param>
      //public DataTable GetAllResponses(int offset, int classID, DataTable currentDT)
      public void GetAllResponses(int offset, int classID, DataTable currentDT)
      {
         if ((CheckConnection()) && (currentDT != null))
         {
            try
            {
               // Get all classes taught by the instructor
               string sql = "SELECT DISTINCT r.id, r.question, r.description, r.type FROM Response r " +
                  "JOIN Question q ON ((q.class = :value1) AND (q.id = r.question)) " +
                  "ORDER BY r.id OFFSET :value0";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Integer));
               command.Parameters[0].Value = offset;
               command.Parameters[1].Value = classID;

               NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
               DataTable dt = new DataTable("Response");
               da.Fill(dt);

               // if Response table already exists, merge new data with existing data
               if (currentDT.TableName == "Response")
               {
                  currentDT.Merge(dt);
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }
      }

      /// <summary>
      /// Makes a query to the connected database to retrieve responses
      /// </summary>
      /// <param name="offset">
      /// Number of rows to skip so that existing data is not retrieved again
      /// </param>
      /// <param name="classID">
      /// ID of the class that is related to the responses
      /// </param>
      /// <param name="currentDT">
      /// Existing data table that contains the local copy of the response data.
      /// Used to merge data from the query with the existing data
      /// </param>
      /// <returns>
      /// Updated data set
      /// </returns>
      public void GetAllEnquiries(int offset, int classID, DataTable currentDT)
      {
         if ((CheckConnection()) && (currentDT != null))
         {
            try
            {
               // Get all enquiries asked to the class
               string sql = "SELECT DISTINCT id, class, time, description FROM Enquiry " +
                  "WHERE class = :value1 " +
                  "ORDER BY id OFFSET :value0";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Integer));
               command.Parameters[0].Value = offset;
               command.Parameters[1].Value = classID;

               NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
               DataTable dt = new DataTable("Enquiry");
               da.Fill(dt);

               // if Enquiry table already exists, merge new data with existing data
               if (currentDT.TableName == "Enquiry")
               {
                  currentDT.Merge(dt);
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }
      }

      /// <summary>
      /// Gets the 'no course' rows that are a part of the basic database schema
      /// </summary>
      /// <returns>
      /// 'No course' rows of the database schema
      /// </returns>
      public DataSet GetNoCourseRows()
      {
         DataSet newData = new DataSet();

         if (CheckConnection())
         {
            try
            {
               // Get no course from course table
               string sql = "SELECT * FROM Course WHERE id = 1";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
               DataTable dt = new DataTable("Course");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get no course from subject table
               sql = "SELECT * FROM Subject WHERE id = 1";

               command = new NpgsqlCommand(sql, dbConn);

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Subject");
               da.Fill(dt);
               newData.Tables.Add(dt);

               // Get no course from term table
               sql = "SELECT * FROM Term WHERE id = 1";

               command = new NpgsqlCommand(sql, dbConn);

               da = new NpgsqlDataAdapter(command);
               dt = new DataTable("Term");
               da.Fill(dt);
               newData.Tables.Add(dt);
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return newData;
      }

      #endregion

      #region DATABASE UPDATE METHODS

      /// <summary>
      /// Updates single value in the database
      /// </summary>
      /// <param name="tableName">
      /// Name of the table.
      /// </param>
      /// <param name="columnName">
      /// Name of the column.
      /// </param>
      /// <param name="id">
      /// ID of the row in the table.
      /// </param>
      /// <param name="value">
      /// New value that is to be updated.
      /// </param>
      public bool UpdateRecord(string tableName, string columnName, int id, object value)
      {
         bool successfulUpdate = false;
         if (CheckConnection())
         {
            try
            {
               string sql = "UPDATE " + tableName + " SET " + columnName + " = " +
                  ":value0 WHERE id = :value1";

               // convert value's .NET type to NpgsqlDbType
               NpgsqlDbType type = GetObjectDBType(value);

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", type));
               command.Parameters.Add(new NpgsqlParameter("value1", NpgsqlDbType.Integer));

               // check if a column is one that much have their values capitalised
               if ((columnName == "session") || (columnName == "code") || (columnName == "type"))
               {
                  command.Parameters[0].Value = value.ToString().ToUpper();
               }
               else
               {
                  if (value.GetType() == typeof(string))
                  {
                     // if the value is a string type of value DEFAULT_STRING
                     // insert capitalised version of the value
                     // else insert value 'as is'
                     string noneString = value.ToString().ToUpper();
                     if (noneString == Defs.DEFAULT_STRING)
                     {
                        command.Parameters[0].Value = noneString;
                     }
                     else
                     {
                        command.Parameters[0].Value = value;
                     }
                  }
                  else
                  {
                     command.Parameters[0].Value = value;
                  }
               }
               command.Parameters[1].Value = id;

               // execute update
               int afffectedCount = command.ExecuteNonQuery();
               if (afffectedCount > 0)
               {
                  successfulUpdate = true;
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return successfulUpdate;
      }

      /// <summary>
      /// Generic method for updating the database
      /// </summary>
      /// <param name="tableName">
      /// Name of the table that is to be modified
      /// </param>
      /// <param name="id">
      /// ID of the record that is to be changed
      /// </param>
      /// <param name="columnNames">
      /// Array of the names of columns that are going to be changed.
      /// Must be the same order as the values in the values array
      /// </param>
      /// <param name="values">
      /// Array of the updated values.
      /// Must be the same order as the names in the columns array
      /// </param>
      /// <returns>
      /// Whether or not the update was successful
      /// </returns>
      public bool UpdateRecord(string tableName, int id, string[] columnNames, object[] values)
      {
         bool result = false;
         if (CheckConnection())
         {
            try
            {
               // generate sql query by looping through the columnNames and values arrays
               string sql = "UPDATE " + tableName + " SET ";
               if (columnNames.Length == values.Length)
               {
                  for (int i = 0; i < columnNames.Length; i++)
                  {
                     if (i > 0)
                     {
                        sql += ", ";
                     }
                     sql += columnNames[i];
                     sql += " = ";
                     string valueString = ":value" + i;
                     sql += valueString;
                  }
               }
               else
               {
                  return false;
               }
               sql += (" WHERE id = :value" + columnNames.Length);

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               // loop through the values array to insert values into the npgsql command
               for (int i = 0; i < values.Length; i++)
               {
                  // convert values[i] .NET type to NpgsqlDbType
                  NpgsqlDbType type = GetObjectDBType(values[i]);

                  string valueString = "value" + i;
                  command.Parameters.Add(new NpgsqlParameter(valueString, type));

                  // check if a column is one that much have their values capitalised
                  if ((columnNames[i] == "session") || (columnNames[i] == "code") || (columnNames[i] == "type"))
                  {
                     command.Parameters[i].Value = values[i].ToString().ToUpper();
                  }
                  else
                  {
                     if (values[i].GetType() == typeof(string))
                     {
                        // if the value is a string type of value DEFAULT_STRING
                        // insert capitalised version of the value
                        // else insert value 'as is'
                        string noneString = values[i].ToString().ToUpper();
                        if (noneString == Defs.DEFAULT_STRING)
                        {
                           command.Parameters[i].Value = noneString;
                        }
                        else
                        {
                           command.Parameters[i].Value = values[i];
                        }
                     }
                     else
                     {
                        command.Parameters[i].Value = values[i];
                     }
                  }
               }
               string idValueString = "value" + columnNames.Length;
               command.Parameters.Add(new NpgsqlParameter(idValueString, NpgsqlDbType.Integer));
               command.Parameters[columnNames.Length].Value = id;

               // execute update
               int affectedRows = (int) command.ExecuteNonQuery();
               if (affectedRows > 0)
               {
                  result = true;
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
               //throw;
            }
         }

         return result;
      }

      /// <summary>
      /// Generic method used to insert values into a database
      /// </summary>
      /// <param name="tableName">
      /// Name of the table which the insert is to occur
      /// </param>
      /// <param name="columnNames">
      /// Names of the columns which correspond to the insert values. 
      /// Must be in the same order as the values array.
      /// </param>
      /// <param name="values">
      /// Values which are to be inserted into the table. 
      /// Must be in the same order as the columnNames array.
      /// </param>
      /// <returns>
      /// ID of the record that was just inserted. Should be either 0 (unsuccessful insert) or 
      /// >= 1 (successful insert).
      /// </returns>
      public int InsertRecord(string tableName, string[] columnNames, object[] values)
      {
         int result = 0;
         if (CheckConnection())
         {
            try
            {
               // generate sql query by looping through the columnNames and values arrays
               string sql = "INSERT INTO " + tableName + "(";
               for (int i = 0; i < columnNames.Length; i++)
               {
                  if (i > 0)
                  {
                     sql += ",";
                  }
                  sql += columnNames[i];
               }
               sql += ") VALUES (";
               for (int i = 0; i < values.Length; i++)
               {
                  if (i > 0)
                  {
                     sql += ",";
                  }
                  string valueString = ":value" + i;
                  sql += valueString;
               }
               sql += ") RETURNING id";

               //MessageBox.Show(sql);
               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               // loop through the values array to insert values into the npgsql command
               for (int i = 0; i < values.Length; i++)
               {
                  // convert values[i] .NET type to NpgsqlDbType
                  NpgsqlDbType type = GetObjectDBType(values[i]);

                  string valueString = "value" + i;
                  command.Parameters.Add(new NpgsqlParameter(valueString, type));

                  // check if a column is one that much have their values capitalised
                  if ((columnNames[i] == "session") || (columnNames[i] == "code") || (columnNames[i] == "type"))
                  {
                     command.Parameters[i].Value = values[i].ToString().ToUpper();
                  }
                  else
                  {
                     if (values[i].GetType() == typeof(string))
                     {
                        // if the value is a string type of value DEFAULT_STRING
                        // insert capitalised version of the value
                        // else insert value 'as is'
                        string noneString = values[i].ToString().ToUpper();
                        if (noneString == Defs.DEFAULT_STRING)
                        {
                           command.Parameters[i].Value = noneString;
                        }
                        else
                        {
                           command.Parameters[i].Value = values[i];
                        }
                     }
                     else
                     {
                        command.Parameters[i].Value = values[i];
                     }
                  }
               }

               // execute insert
               result = (int) command.ExecuteScalar();
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
               //throw;
            }
         }

         return result;
      }

      /// <summary>
      /// Generic method for deleting records from a table given an array of column names as criteria
      /// </summary>
      /// <param name="tableName">
      /// Name of the table which is going to have a record deleted
      /// </param>
      /// <param name="columnNames">
      /// Names of the columns which are to be used as parameters for the deletion
      /// </param>
      /// <param name="values">
      /// Values which are to be used as parameters for comparision in the delete statement
      /// </param>
      /// <returns>
      /// Whether or not the deletion was successful or not
      /// </returns>
      public bool DeleteRecord(string tableName, string[] columnNames, object[] values)
      {
         bool successfulDelete = false;

         if (CheckConnection())
         {
            try
            {

               // generate sql query by looping through the columnNames and values arrays
               string sql = "DELETE FROM " + tableName + " WHERE ";

               if (columnNames.Length == values.Length)
               {
                  for (int i = 0; i < columnNames.Length; i++)
                  {
                     if (i > 0)
                     {
                        sql += " AND ";
                     }
                     sql += columnNames[i];
                     sql += " = ";
                     string valueString = ":value" + i;
                     sql += valueString;
                  }
               }
               else
               {
                  return false;
               }

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);

               // loop through the values array to insert values into the npgsql command
               for (int i = 0; i < values.Length; i++)
               {
                  // convert values[i] .NET type to NpgsqlDbType
                  NpgsqlDbType type = GetObjectDBType(values[i]);

                  string valueString = "value" + i;
                  command.Parameters.Add(new NpgsqlParameter(valueString, type));

                  // check if a column is one that much have their values capitalised
                  if ((columnNames[i] == "session") || (columnNames[i] == "code") || (columnNames[i] == "type"))
                  {
                     command.Parameters[i].Value = values[i].ToString().ToUpper();
                  }
                  else
                  {
                     if (values[i].GetType() == typeof(string))
                     {
                        // if the value is a string type of value DEFAULT_STRING
                        // insert capitalised version of the value
                        // else insert value 'as is'
                        string noneString = values[i].ToString().ToUpper();
                        if (noneString == Defs.DEFAULT_STRING)
                        {
                           command.Parameters[i].Value = noneString;
                        }
                        else
                        {
                           command.Parameters[i].Value = values[i];
                        }
                     }
                     else
                     {
                        command.Parameters[i].Value = values[i];
                     }
                  }
               }

               // execute delete
               int afffectedCount = command.ExecuteNonQuery();
               if (afffectedCount > 0)
               {
                  successfulDelete = true;
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return successfulDelete;
      }

      /// <summary>
      /// Generic method for deleting a specific record with a given ID
      /// </summary>
      /// <param name="tableName">
      /// Name of the table which is going to have a record deleted
      /// </param>
      /// <param name="id">
      /// ID of the record that's to be deleted
      /// </param>
      /// <returns>
      /// Whether or not the deletion was successful or not
      /// </returns>
      public bool DeleteRecord(string tableName, int id)
      {
         bool successfulDelete = false;

         if (CheckConnection())
         {
            try
            {
               // create sql query string
               string sql = "DELETE FROM " + tableName + " WHERE id = :value0";

               NpgsqlCommand command = new NpgsqlCommand(sql, dbConn);
               command.Parameters.Add(new NpgsqlParameter("value0", NpgsqlDbType.Integer));
               command.Parameters[0].Value = id;

               // execute delete
               int afffectedCount = command.ExecuteNonQuery();
               if (afffectedCount > 0)
               {
                  successfulDelete = true;
               }
            }
            catch (Exception error)
            {
               Console.WriteLine(error.ToString());

               resultMsg = error.ToString();
            }
         }

         return successfulDelete;
      }

      #endregion

      #region HELPER METHODS

      private NpgsqlDbType GetObjectDBType(object value)
      {
         NpgsqlDbType type = NpgsqlDbType.Text;

         if (value.GetType() == typeof(int))
         {
            type = NpgsqlDbType.Integer;
         }
         else if (value.GetType() == typeof(DateTime))
         {
            type = NpgsqlDbType.Timestamp;
         }
         else if (value.GetType() == typeof(bool))
         {
            type = NpgsqlDbType.Boolean;
         }
         else
         {
            type = NpgsqlDbType.Text;
         }

         return type;
      }

      #endregion

      #region PROPERTIES

      /// <summary>
      /// Gets status message of the most recent activity performed by this object.
      /// </summary>
      public string ResultMessage
      {
         get
         {
            return resultMsg;
         }
      }

      /// <summary>
      /// Gets IP address used to connect to the database server.
      /// </summary>
      public string ServerAddress
      {
         get
         {
            return server;
         }
      }

      /// <summary>
      /// Gets port used to connect to the database server.
      /// </summary>
      public string ServerPort
      {
         get
         {
            return port;
         }
      }

      /// <summary>
      /// Gets username used to connect to the database server.
      /// </summary>
      public string Username
      {
         get
         {
            return userID;
         }
      }

      /// <summary>
      /// Gets password used to connect to the database server.
      /// </summary>
      public string UserPassword
      {
         get
         {
            return password;
         }
      }

      /// <summary>
      /// Gets name of the database the object is connected to.
      /// </summary>
      public string DatabaseName
      {
         get
         {
            return dbName;
         }
      }

      #endregion
   }
}
